package com.bcu.cn.onlineexamsystem.grademanage.mapper;


import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


import java.util.List;
import java.util.Map;

/**
 * 教师端成绩分析数据访问层
 */
@Mapper
public interface TeacherScoreAnalysisMapper {
    
    /**
     * 获取总体成绩统计
     */
    @Select("""
        SELECT 
            COUNT(DISTINCT se.student_id) as totalStudents,
            COUNT(*) as totalExams,
            ROUND(AVG(se.total_score), 2) as averageScore,
            MAX(se.total_score) as highestScore,
            MIN(se.total_score) as lowestScore,
            SUM(CASE WHEN se.total_score >= e.total_score * 0.6 THEN 1 ELSE 0 END) as passCount,
            SUM(CASE WHEN se.total_score < e.total_score * 0.6 THEN 1 ELSE 0 END) as failCount,
            ROUND(SUM(CASE WHEN se.total_score >= e.total_score * 0.6 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as passRate
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        WHERE se.status = 2
    """)
    Map<String, Object> getScoreStatistics();
    
    /**
     * 根据课程ID获取成绩统计
     */
    @Select("""
        SELECT 
            COUNT(DISTINCT se.student_id) as totalStudents,
            COUNT(*) as totalExams,
            ROUND(AVG(se.total_score), 2) as averageScore,
            MAX(se.total_score) as highestScore,
            MIN(se.total_score) as lowestScore,
            SUM(CASE WHEN se.total_score >= e.total_score * 0.6 THEN 1 ELSE 0 END) as passCount,
            SUM(CASE WHEN se.total_score < e.total_score * 0.6 THEN 1 ELSE 0 END) as failCount,
            ROUND(SUM(CASE WHEN se.total_score >= e.total_score * 0.6 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as passRate
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        WHERE se.status = 2 AND e.course_id = #{courseId}
    """)
    Map<String, Object> getScoreStatisticsByCourse(@Param("courseId") Long courseId);
    
    /**
     * 获取成绩趋势数据
     */
    @Select("""
        SELECT 
            DATE_FORMAT(e.start_time, '%Y-%m-%d') as examDate,
            e.exam_name as examName,
            ROUND(AVG(se.total_score), 2) as averageScore
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        WHERE se.status = 2
        GROUP BY e.exam_id, e.exam_name, DATE_FORMAT(e.start_time, '%Y-%m-%d')
        ORDER BY e.start_time ASC
        LIMIT 10
    """)
    List<Map<String, Object>> getScoreTrend();
    
    /**
     * 根据课程ID获取成绩趋势数据
     */
    @Select("""
        SELECT 
            DATE_FORMAT(e.start_time, '%Y-%m-%d') as examDate,
            e.exam_name as examName,
            ROUND(AVG(se.total_score), 2) as averageScore
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        WHERE se.status = 2 AND e.course_id = #{courseId}
        GROUP BY e.exam_id, e.exam_name, DATE_FORMAT(e.start_time, '%Y-%m-%d')
        ORDER BY e.start_time ASC
        LIMIT 10
    """)
    List<Map<String, Object>> getScoreTrendByCourse(@Param("courseId") Long courseId);
    
    /**
     * 获取成绩分布数据
     */
    @Select("""
        SELECT 
            CASE 
                WHEN se.total_score >= 90 THEN '90-100'
                WHEN se.total_score >= 80 THEN '80-89'
                WHEN se.total_score >= 70 THEN '70-79'
                WHEN se.total_score >= 60 THEN '60-69'
                ELSE '0-59'
            END as scoreRange,
            COUNT(*) as count
        FROM student_exams se
        WHERE se.status = 2
        GROUP BY 
            CASE 
                WHEN se.total_score >= 90 THEN '90-100'
                WHEN se.total_score >= 80 THEN '80-89'
                WHEN se.total_score >= 70 THEN '70-79'
                WHEN se.total_score >= 60 THEN '60-69'
                ELSE '0-59'
            END
        ORDER BY 
            CASE scoreRange
                WHEN '90-100' THEN 1
                WHEN '80-89' THEN 2
                WHEN '70-79' THEN 3
                WHEN '60-69' THEN 4
                ELSE 5
            END
    """)
    List<Map<String, Object>> getScoreDistribution();
    
    /**
     * 根据课程ID获取成绩分布数据
     */
    @Select("""
        SELECT 
            CASE 
                WHEN se.total_score >= 90 THEN '90-100'
                WHEN se.total_score >= 80 THEN '80-89'
                WHEN se.total_score >= 70 THEN '70-79'
                WHEN se.total_score >= 60 THEN '60-69'
                ELSE '0-59'
            END as scoreRange,
            COUNT(*) as count
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        WHERE se.status = 2 AND e.course_id = #{courseId}
        GROUP BY 
            CASE 
                WHEN se.total_score >= 90 THEN '90-100'
                WHEN se.total_score >= 80 THEN '80-89'
                WHEN se.total_score >= 70 THEN '70-79'
                WHEN se.total_score >= 60 THEN '60-69'
                ELSE '0-59'
            END
        ORDER BY 
            CASE scoreRange
                WHEN '90-100' THEN 1
                WHEN '80-89' THEN 2
                WHEN '70-79' THEN 3
                WHEN '60-69' THEN 4
                ELSE 5
            END
    """)
    List<Map<String, Object>> getScoreDistributionByCourse(@Param("courseId") Long courseId);
    
    /**
     * 获取课程成绩分析
     */
    @Select("""
        SELECT 
            c.course_name as courseName,
            ROUND(AVG(se.total_score), 2) as averageScore,
            COUNT(DISTINCT e.exam_id) as examCount,
            COUNT(DISTINCT se.student_id) as studentCount
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        JOIN courses c ON e.course_id = c.course_id
        WHERE se.status = 2
        GROUP BY c.course_id, c.course_name
        ORDER BY averageScore DESC
    """)
    List<Map<String, Object>> getCourseScoreAnalysis();
    
    /**
     * 获取学生成绩排名
     */
    @Select("""
        SELECT 
            se.student_id as studentId,
            u.user_name as studentName,
            ROUND(AVG(se.total_score), 2) as averageScore,
            COUNT(*) as examCount,
            RANK() OVER (ORDER BY AVG(se.total_score) DESC) as `rank`
        FROM student_exams se
        JOIN users u ON se.student_id = u.user_id
        WHERE se.status = 2 AND u.role = 'student'
        GROUP BY se.student_id, u.user_name
        ORDER BY averageScore DESC
        LIMIT 20
    """)
    List<Map<String, Object>> getStudentRanking();
    
    /**
     * 根据课程ID获取学生成绩排名
     */
    @Select("""
        SELECT 
            se.student_id as studentId,
            u.user_name as studentName,
            ROUND(AVG(se.total_score), 2) as averageScore,
            COUNT(*) as examCount,
            RANK() OVER (ORDER BY AVG(se.total_score) DESC) as `rank`
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        JOIN users u ON se.student_id = u.user_id
        WHERE se.status = 2 AND e.course_id = #{courseId} AND u.role = 'student'
        GROUP BY se.student_id, u.user_name
        ORDER BY averageScore DESC
        LIMIT 20
    """)
    List<Map<String, Object>> getStudentRankingByCourse(@Param("courseId") Long courseId);
    
    /**
     * 获取最近考试记录
     */
    @Select("""
        SELECT 
            se.record_id as recordId,
            u.user_name as studentName,
            se.student_id as studentId,
            e.exam_name as examName,
            c.course_name as courseName,
            e.start_time as examTime,
            se.total_score as score,
            e.total_score as totalScore,
            se.status
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        JOIN courses c ON e.course_id = c.course_id
        JOIN users u ON se.student_id = u.user_id
        WHERE se.status = 2
        ORDER BY e.start_time DESC
        LIMIT #{limit}
    """)
    List<Map<String, Object>> getRecentExamRecords(Integer limit);
    
    /**
     * 根据课程ID获取最近考试记录
     */
    @Select("""
        SELECT 
            se.record_id as recordId,
            u.user_name as studentName,
            se.student_id as studentId,
            e.exam_name as examName,
            c.course_name as courseName,
            e.start_time as examTime,
            se.total_score as score,
            e.total_score as totalScore,
            se.status
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        JOIN courses c ON e.course_id = c.course_id
        JOIN users u ON se.student_id = u.user_id
        WHERE se.status = 2 AND e.course_id = #{courseId}
        ORDER BY e.start_time DESC
        LIMIT #{limit}
    """)
    List<Map<String, Object>> getRecentExamRecordsByCourse(@Param("courseId") Long courseId, @Param("limit") Integer limit);
    
    /**
     * 根据课程ID获取课程成绩分析详情
     */
    @Select("""
        SELECT 
            e.exam_name as examName,
            ROUND(AVG(se.total_score), 2) as averageScore,
            COUNT(*) as examCount
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        WHERE se.status = 2 AND e.course_id = #{courseId}
        GROUP BY e.exam_id, e.exam_name
        ORDER BY e.start_time ASC
    """)
    List<Map<String, Object>> getCourseDetailAnalysis(Long courseId);
    
    /**
     * 根据考试ID获取考试详情
     */
    @Select("""
        SELECT 
            se.record_id as recordId,
            u.user_name as studentName,
            se.student_id as studentId,
            e.exam_name as examName,
            c.course_name as courseName,
            e.start_time as examTime,
            se.total_score as score,
            e.total_score as totalScore,
            se.status,
            ROUND(se.total_score * 100.0 / e.total_score, 2) as percentage
        FROM student_exams se
        JOIN exams e ON se.exam_id = e.exam_id
        JOIN courses c ON e.course_id = c.course_id
        JOIN users u ON se.student_id = u.user_id
        WHERE se.exam_id = #{examId} AND se.status = 2
        ORDER BY se.total_score DESC
    """)
    List<Map<String, Object>> getExamDetail(Long examId);
    
    /**
     * 获取教师的所有课程列表
     */
    @Select("""
        SELECT 
            c.course_id as courseId,
            c.course_name as courseName,
            c.course_code as courseCode
        FROM courses c
        JOIN teacher_courses tc ON c.course_id = tc.course_id
        WHERE tc.teacher_id = #{teacherId}
        ORDER BY c.course_name
    """)
    List<Map<String, Object>> getTeacherCourses(@Param("teacherId") String teacherId);
} 